﻿<%@ Page Title="" Language="C#" MasterPageFile="~/Main.master" AutoEventWireup="true" CodeBehind="StockList.aspx.cs" Inherits="Project_Udrinkwater.Stock.StockList" %>
<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server">
   
    <script language="javascript" type="text/javascript">
            // <![CDATA[
        
        function OnCategory1Changed(cmbParent) {
            if (isUpdating)
                return;
            var comboValue = cmbParent.GetSelectedItem().value;
            if (comboValue)
                grid.GetEditor("Product_Category_ID").PerformCallback(comboValue.toString());
        }
        
        
        var combo = null;
        var isUpdating = false;
            // ]]>
        </script>
        <fieldset>
        <legend>รายการเบิก/รับสินค้า</legend>
    <asp:Panel ID="Panel1" runat="server">
        <table style="width: 100%">
            <tr>
                <td align="right" style="width: 150px">
                    &nbsp;</td>
                <td style="width: 276px">
                    &nbsp;</td>
                <td style="width: 85px">
                    &nbsp;</td>
                <td align="right">
                    <dx:ASPxButton ID="ASPxButton1" runat="server" onclick="ASPxButton1_Click" 
                        Text="พิมพ์ใบเบิก/รับสินค้า" Width="150px">
                    </dx:ASPxButton>
                </td>
            </tr>
            <tr>
                <td align="right" style="width: 150px">
                    <asp:Label ID="Label1" runat="server" Text="เลขที่เอกสาร:"></asp:Label>
                </td>
                <td style="width: 276px">
                    <dx:ASPxTextBox ID="txtDocNo" runat="server" ReadOnly="True" Width="250px">
                    </dx:ASPxTextBox>
                </td>
                <td style="width: 85px">
                    <asp:Label ID="Label2" runat="server" Text="วันที่เอกสาร:"></asp:Label>
                </td>
                <td>
                    <dx:ASPxTextBox ID="txtDateDoc" runat="server" ReadOnly="True" 
                        style="margin-bottom: 0px" Width="250px">
                    </dx:ASPxTextBox>
                </td>
            </tr>
            <tr>
                <td align="right" style="width: 150px">
                    &nbsp;</td>
                <td style="width: 276px">
                    &nbsp;</td>
                <td style="width: 85px">
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td align="right" style="width: 150px">
                    <asp:Label ID="Label3" runat="server" Text="พนักงานทำรายการ:"></asp:Label>
                </td>
                <td style="width: 276px">
                    <dx:ASPxComboBox ID="cmbEmp" runat="server" DataSourceID="sdsEmp" 
                        ReadOnly="True" TextField="Name" ValueField="Employee_ID" Width="250px">
                        <Columns>
                            <dx:ListBoxColumn FieldName="Employee_ID" />
                            <dx:ListBoxColumn FieldName="Name" />
                            <dx:ListBoxColumn FieldName="Department_Name" />
                        </Columns>
                    </dx:ASPxComboBox>
                </td>
                <td style="width: 85px">
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td style="width: 150px">
                    &nbsp;</td>
                <td style="width: 276px">
                    &nbsp;</td>
                <td style="width: 85px">
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
        </table>
    </asp:Panel>
    
    <dx:ASPxGridView ID="ASPxGridView2" runat="server" AutoGenerateColumns="False" 
                    DataSourceID="sdsListStock" Width="100%" 
                    onbeforeperformdataselect="ASPxGridView2_BeforePerformDataSelect" 
                    KeyFieldName="id" ClientInstanceName="grid" 
                    onrowvalidating="ASPxGridView2_RowValidating" 
        onstartrowediting="ASPxGridView2_StartRowEditing" 
        oncustomcallback="ASPxGridView2_CustomCallback">
        <Columns>
            <dx:GridViewCommandColumn VisibleIndex="11" Caption="ทำรายการ">
                <EditButton Visible="True" Text="แก้ไข">
                </EditButton>
                <NewButton Visible="True" Text="เพิ่ม">
                </NewButton>
                <DeleteButton Visible="True" Text="ลบ">
                </DeleteButton>
                <CancelButton Text="ยกเลิก">
                </CancelButton>
                <UpdateButton Text="ตกลง">
                </UpdateButton>
                <ClearFilterButton Text="ยกเลิก" Visible="True">
                </ClearFilterButton>
            </dx:GridViewCommandColumn>
            <dx:GridViewDataTextColumn FieldName="id" VisibleIndex="0" 
                            Visible="False" ReadOnly="True">
                <EditFormSettings Visible="False" />
            </dx:GridViewDataTextColumn>
            <dx:GridViewDataTextColumn FieldName="Stock_ID" 
                            VisibleIndex="2" Visible="False">
            </dx:GridViewDataTextColumn>
            <dx:GridViewDataComboBoxColumn Caption="ประภทสินค้า" 
                FieldName="Product_Category_ID" VisibleIndex="5">
                <PropertiesComboBox DataSourceID="SqlProductCateAll" TextField="Product_Category_Name" 
                    ValueField="Product_Category_ID">
                </PropertiesComboBox>
            </dx:GridViewDataComboBoxColumn>
            <dx:GridViewDataTextColumn VisibleIndex="3" FieldName="Row" ReadOnly="True" 
                Caption="ลำดับ" Width="50px">
                <EditFormSettings Visible="False" />
            </dx:GridViewDataTextColumn>
            <dx:GridViewDataComboBoxColumn Caption="ประเภทการโอนย้าย" 
                FieldName="Transfer_Category_ID" VisibleIndex="6">
                <PropertiesComboBox DataSourceID="sdsTranferCate" 
                    TextField="Transfer_Category_Name" ValueField="Transfer_Category_ID">
                </PropertiesComboBox>
            </dx:GridViewDataComboBoxColumn>
            <dx:GridViewDataComboBoxColumn Caption="โกดัง" FieldName="Warehouse_ID" 
                VisibleIndex="7">
                <PropertiesComboBox DataSourceID="sdsWarehouse" TextField="Warehouse_name" 
                    ValueField="Warehouse_ID">
                </PropertiesComboBox>
            </dx:GridViewDataComboBoxColumn>
            <dx:GridViewDataTextColumn FieldName="Amount" VisibleIndex="9" 
                Caption="จำนวน">
            </dx:GridViewDataTextColumn>
            <dx:GridViewDataComboBoxColumn FieldName="Product_ID" VisibleIndex="4" 
                Caption="สินค้า" Width="300px">
                <PropertiesComboBox DataSourceID="sqlProduct" TextField="ProductName" 
                    ValueField="Product_ID">
                          <ClientSideEvents SelectedIndexChanged="function(s, e) { OnCategory1Changed(s); }"></ClientSideEvents>
                </PropertiesComboBox>
            </dx:GridViewDataComboBoxColumn>
            <dx:GridViewDataTextColumn FieldName="Stock_doc_NO" ReadOnly="True" 
                VisibleIndex="1" Visible="False">
                <EditFormSettings Visible="False" />
            </dx:GridViewDataTextColumn>
        </Columns>
        <Settings ShowFilterRow="True" />
    </dx:ASPxGridView>
    </fieldset>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:DB_UdrinkConnectionString %>" 
    DeleteCommand="DELETE FROM TB_Stock WHERE (Stock_ID = @Stock_ID)" 
    InsertCommand="INSERT INTO TB_Stock(Stock_Date, Act_Employee_ID) VALUES (@Stock_Date, @Act_Employee_ID)" 
    SelectCommand="SELECT Stock_ID, Stock_doc_NO, Stock_Date, Act_Employee_ID FROM TB_Stock WHERE (Stock_ID = @Stock_ID) " 
    
        
        UpdateCommand="UPDATE TB_Stock SET Stock_Date =?, Act_Employee_ID =?" 
        onselecting="SqlDataSource1_Selecting">
        <DeleteParameters>
            <asp:Parameter Name="Stock_ID" />
        </DeleteParameters>
        <InsertParameters>
            <asp:Parameter Name="Stock_Date" />
            <asp:Parameter Name="Act_Employee_ID" />
        </InsertParameters>
        <SelectParameters>
            <asp:Parameter Name="Stock_ID" />
        </SelectParameters>
</asp:SqlDataSource>
    <asp:SqlDataSource ID="sdsListStock" runat="server" 
        ConnectionString="<%$ ConnectionStrings:DB_UdrinkConnectionString %>" 
        
        SelectCommand="SELECT TB_Stock_List.id, TB_Stock_List.Stock_ID, TB_Stock_List.Product_Category_ID,ROW_NUMBER() OVER(ORDER BY TB_Stock_List.Stock_ID DESC) AS Row,TB_Stock_List.Transfer_Category_ID, TB_Stock_List.Warehouse_ID, TB_Stock_List.Amount, TB_Stock_List.Product_ID, TB_Stock.Stock_ID AS Expr1, TB_Stock.Stock_doc_NO FROM TB_Stock_List INNER JOIN TB_Stock ON TB_Stock_List.Stock_ID = TB_Stock.Stock_ID CROSS JOIN TB_Product WHERE TB_Stock.Stock_ID=@Stock_ID GROUP BY TB_Stock_List.id, TB_Stock_List.Stock_ID, TB_Stock_List.Product_Category_ID, TB_Stock_List.Transfer_Category_ID, TB_Stock_List.Warehouse_ID, TB_Stock_List.Amount, TB_Stock_List.Product_ID, TB_Stock.Stock_ID, TB_Stock.Stock_doc_NO;" 
        DeleteCommand="UPDATE
    tb2
SET 
  tb2.In_Stock = CASE WHEN  tb1.Transfer_Category_ID = 1 THEN tb2.In_Stock + tb1.Amount ELSE tb2.In_Stock - tb1.Amount END
  from	TB_Stock_List tb1  inner join TB_Product tb2
	on	tb1.Product_ID = tb2.Product_ID
WHERE
     tb1.id = @id
DELETE FROM TB_Stock_List WHERE (id = @id);" 
        InsertCommand="INSERT INTO TB_Stock_List(Stock_ID, Product_ID, Product_Category_ID, Transfer_Category_ID,Warehouse_ID, Amount) VALUES (@Stock_ID, @Product_ID, @Product_Category_ID, @Transfer_Category_ID, @Warehouse_ID, @Amount) ;UPDATE
    TB_Product
SET 
  In_Stock = CASE WHEN  @Transfer_Category_ID = 1 THEN In_Stock - @Amount ELSE In_Stock + @Amount END
  
WHERE
    (Product_ID = @Product_ID)
" 
        
        
        
        
        
        
        
        
        UpdateCommand="UPDATE TB_Stock_List SET Product_ID =@Product_ID, Product_Category_ID =@Product_Category_ID, Transfer_Category_ID =@Transfer_Category_ID, Warehouse_ID =@Warehouse_ID, Amount =@Amount WHERE id=@id;UPDATE TB_Product SET In_Stock=(SELECT SUM(Amount)  FROM TB_Stock_List WHERE Transfer_Category_ID=2)  
WHERE
    (Product_ID = @Product_ID)">
        <DeleteParameters>
            <asp:Parameter Name="id" />
        </DeleteParameters>
        <InsertParameters>
            <asp:SessionParameter Name="Stock_ID" SessionField="Stock_ID" />
            <asp:Parameter Name="Product_ID" />
            <asp:Parameter Name="Product_Category_ID" />
            <asp:Parameter Name="Transfer_Category_ID" />
            <asp:Parameter Name="Warehouse_ID" />
            <asp:Parameter Name="Amount" />
            
        </InsertParameters>
        <SelectParameters>
            <asp:SessionParameter Name="Stock_ID" SessionField="Stock_ID" />
        </SelectParameters>
        <UpdateParameters>
            <asp:Parameter Name="Product_ID" />
             <asp:Parameter Name="Product_Category_ID" />
            <asp:Parameter Name="Transfer_Category_ID" />
               <asp:Parameter Name="Warehouse_ID" />
               <asp:Parameter Name="Amount" />
            <asp:Parameter Name="id" />
        </UpdateParameters>
    </asp:SqlDataSource>
    <asp:SqlDataSource ID="sdsFloor" runat="server" 
        ConnectionString="<%$ ConnectionStrings:DB_UdrinkConnectionString %>" 
        
        SelectCommand="SELECT [Floor_ID], [Floor_Name] FROM [TB_Floor]">
    </asp:SqlDataSource>
    <asp:SqlDataSource ID="sdsWarehouse" runat="server" 
        ConnectionString="<%$ ConnectionStrings:DB_UdrinkConnectionString %>" 
        SelectCommand="SELECT [Warehouse_name], [Warehouse_ID] FROM [TB_Warehouse]">
    </asp:SqlDataSource>
    <asp:SqlDataSource ID="sdsTranferCate" runat="server" 
        ConnectionString="<%$ ConnectionStrings:DB_UdrinkConnectionString %>" 
        SelectCommand="SELECT * FROM [TB_Tranfer_Category]">
    </asp:SqlDataSource>
    <asp:SqlDataSource ID="sdsEmp" runat="server" 
        ConnectionString="<%$ ConnectionStrings:DB_UdrinkConnectionString %>" 
        
        
        SelectCommand="SELECT TB_Employee.Employee_ID, TB_Employee.FirstName + ' ' + TB_Employee.LastName AS Name, TB_Department.Department_Name FROM TB_Employee INNER JOIN TB_Department ON TB_Employee.Departmernt_ID = TB_Department.Department_ID WHERE (TB_Employee.Employee_ID = @Employee_ID)" 
        onselecting="sdsEmp_Selecting">
        <SelectParameters>
            <asp:Parameter Name="Employee_ID" />
        </SelectParameters>
    </asp:SqlDataSource>
    <asp:SqlDataSource ID="sqlProcate" runat="server" 
        ConnectionString="<%$ ConnectionStrings:DB_UdrinkConnectionString %>" 
        
        
        SelectCommand="SELECT TB_Product_Category.Product_Category_ID, TB_Product_Category.Product_Category_Name FROM TB_Product INNER JOIN TB_Product_Category ON TB_Product.Product_Category_ID = TB_Product_Category.Product_Category_ID  WHERE (TB_Product.Product_ID = @Product_ID)">
        <SelectParameters>
            <asp:Parameter Name="Product_ID" />
        </SelectParameters>
    </asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlProductCateAll" runat="server" 
        ConnectionString="<%$ ConnectionStrings:DB_UdrinkConnectionString %>" 
        SelectCommand="SELECT * FROM [TB_Product_Category]"></asp:SqlDataSource>
    <asp:SqlDataSource ID="sqlProduct" runat="server" 
        ConnectionString="<%$ ConnectionStrings:DB_UdrinkConnectionString %>" 
        
        
        
        
        SelectCommand="SELECT TB_Product.Product_ID, TB_Product.Product_Name + ' : จำนวนทีเหลือในคลัง ' + CONVERT (nvarchar, TB_Product.In_Stock) + ' ' + (SELECT Unit_Name FROM TB_Unit_Category WHERE (TB_Product.Product_Unit = Unit_ID)) AS ProductName, TB_Product.In_Stock, TB_Product_Category.Product_Category_Name, TB_Product.Product_Category_ID FROM TB_Product INNER JOIN TB_Product_Category ON TB_Product.Product_Category_ID = TB_Product_Category.Product_Category_ID
">
    </asp:SqlDataSource>
    <asp:SqlDataSource ID="sqlUnit" runat="server" 
        ConnectionString="<%$ ConnectionStrings:DB_UdrinkConnectionString %>" 
        SelectCommand="SELECT * FROM [TB_Unit_Category]"></asp:SqlDataSource>
    <asp:SqlDataSource ID="sqlValStock" runat="server" 
        ConnectionString="<%$ ConnectionStrings:DB_UdrinkConnectionString %>" 
        SelectCommand="SELECT [Product_ID], [In_Stock] FROM [TB_Product] WHERE ([Product_ID] = @Product_ID)"
        onselecting="sqlValStock_Selecting" >
        
        <SelectParameters>
            <asp:SessionParameter Name="Product_ID" SessionField="Product_ID" 
                Type="String" />
        </SelectParameters>
    </asp:SqlDataSource>
    <asp:SqlDataSource ID="sqlProductAmountAll" runat="server" 
        ConnectionString="<%$ ConnectionStrings:DB_UdrinkConnectionString %>" 
        SelectCommand="SELECT [In_Stock], [Product_ID] FROM [TB_Product]"></asp:SqlDataSource>
    <asp:SqlDataSource ID="sqlProductAmount" runat="server" 
        ConnectionString="<%$ ConnectionStrings:DB_UdrinkConnectionString %>" 
        
        SelectCommand="SELECT In_Stock, Product_ID FROM TB_Product WHERE (Product_ID = @Product_ID)">
        <SelectParameters>
            <asp:Parameter Name="Product_ID" />
        </SelectParameters>
    </asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
        ConnectionString="<%$ ConnectionStrings:DB_UdrinkConnectionString %>" 
        SelectCommand="SELECT [Floor_ID], [Floor_Name] FROM [TB_Floor] WHERE ([Warehouse_ID] = @Warehouse_ID)">
        <SelectParameters>
            <asp:Parameter DefaultValue="@Warehouse_ID" Name="Warehouse_ID" Type="String" />
        </SelectParameters>
    </asp:SqlDataSource>
    <asp:SqlDataSource ID="sqlInStock" runat="server" 
        ConnectionString="<%$ ConnectionStrings:DB_UdrinkConnectionString %>" 
        SelectCommand="SELECT TB_Product.Product_ID, TB_InStock.In_Stock FROM TB_InStock INNER JOIN TB_Product ON TB_InStock.Product_ID = TB_Product.Product_ID">
    </asp:SqlDataSource>
    <br />
</asp:Content>
